-- This demo shows some weirdness with the world's most commonly used collation,
-- SQL_Latin1_General_CP1_CI_AS.
-- © Erland Sommarskog 2024.

-- Create a database and move there.
CREATE DATABASE SQL_Latin1_General_CP1_CI_AS 
        COLLATE SQL_Latin1_General_CP1_CI_AS
go
USE SQL_Latin1_General_CP1_CI_AS
go
-- Create a table with both char and nchar, writing the same characters to
-- both.
CREATE TABLE sortchars(no  int      NOT NULL,
                       ch  char(1)  NOT NULL,
                       nch nchar(1) NOT NULL,
                       CONSTRAINT pk_sortchars PRIMARY KEY (no)
)
go
-- Numbering relates to the result when sorting by nch.
INSERT sortchars(no, ch, nch)
   VALUES(1, ' ', N' '),
         (2, '-', N'-'),
         (3, '@', N'@'),
         (4, '+', N'+'),
         (5, '1', N'1'),
         (6, 'A', N'A'),
         (7, 'Š', N'Š'),
         (8, 'š', N'š'),
         (9, 'Z', N'Z')

-- 1) Run as-is. 2) Activate the COLLATE clause. 3) Change nch to ch in ORDER BY.
-- 4) Run without the COLLATE again - result is quite different.
SELECT no, ch, nch
FROM   sortchars
ORDER  BY nch 
    -- COLLATE Latin1_General_CI_AS
go
-- Look how this purportedly case-insensitive collation handles Š in varchar!
SELECT 'Košice' AS name,
       IIF( 'Košice' =  'KOŠICE', 'Equal', 'Not Equal') AS varchar,
       IIF( 'Košice' =  'KOšICE', 'Equal', 'Not Equal') AS varchar2,
       IIF(N'Košice' = N'KOŠICE', 'Equal', 'Not Equal') AS nvarchar
go
-- What implications may that have for indexes?




